package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.*;
import csu.web.mypetstore.persistence.AccountDao;
import csu.web.mypetstore.persistence.DBUtil;
import csu.web.mypetstore.service.CatalogService;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;

public class AccountDaoImpl implements AccountDao {
    private static final String GET_ACCOUNT_BY_USERNAME = "SELECT " +
            "SIGNON.USERNAME,SIGNON.PASSWORD AS password," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME " +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
            "WHERE ACCOUNT.USERID = ? " +
            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
            "AND PROFILE.USERID = ACCOUNT.USERID " +
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT " +
            "SIGNON.USERNAME,SIGNON.PASSWORD AS password," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME " +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
            "WHERE ACCOUNT.USERID = ? AND SIGNON.PASSWORD = ? " +
            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
            "AND PROFILE.USERID = ACCOUNT.USERID " +
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String GET_MYCART_BY_USERNAME_AND_ITEMID =
            "SELECT  user,iditem,QUANTITY FROM mycart WHERE USER = ? AND IDITEM = ?";
    private static final String READ_MY_CART =
            "SELECT  user,iditem,QUANTITY FROM mycart WHERE USER = ? ";

    private static final String INSERT_SIGNON =
            "insert into signon (username,password) values(?,?)";

    private static final String INSERT_PROFILE =
            "insert into profile (userid,langpref,favcategory,mylistopt,banneropt) values (?,?,?,?,?)";


    private static final String RESERVE_CART =
            "insert into mycart(user,iditem,quantity)values(?,?,?)";
    private static final String INSERT_ACCOUNT =
            "insert into account (userid,email,firstname,lastname,status,addr1,addr2,city,state,zip,country,phone) values(?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String UPDATE_CART =
            "UPDATE mycart SET quantity=? WHERE user=? AND iditem=? ";
    private static final String UPDATE_OLD_ACCOUNT =
            "update account set userid=?, email=?, firstname=?, lastname=?, addr1=?, addr2=?, state=?, phone=?, zip=?, country=?, city=? where userid=?";
    private static final String CHANGE_ACCOUNT =
            "update account,signon,profile set signon.username=?,signon.password=?,profile.userid=?,profile.favcategory=?,profile.banneropt=?,profile.mylistopt=?," +
                    "account.userid=?,account.email=?,account.addr1=?,account.addr2=?,account.firstname=?,account.lastname=?,account.state=?,account.city=?," +
                    "account.country=?,account.phone=?,account.zip=? where signon.username =? and signon.password=? and account.userid = signon.username and profile.userid=signon.username";
    @Override
    public Account getAccountByUsername(String username) {
        Account accountResult = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1, username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = this.resultSetToAccount(resultSet);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountResult;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = this.resultSetToAccount(resultSet);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountResult;
    }

    @Override
    public MyCart getMyCartByUsernameAnditemId(String username,String itemId) {
        MyCart myCartResult = null;
        try {
            Connection newconnection = DBUtil.getConnection();
            PreparedStatement newpreparedStatement = newconnection.prepareStatement(GET_MYCART_BY_USERNAME_AND_ITEMID);
            newpreparedStatement.setString(1, username);
            newpreparedStatement.setString(2, itemId);
            ResultSet resultSet = newpreparedStatement.executeQuery();
            if (resultSet.next()){
                myCartResult = this.resultSetToMyCart(resultSet);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(newpreparedStatement);
            DBUtil.closeConnection(newconnection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return myCartResult;
    }
    @Override
    public void updateaccount(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_OLD_ACCOUNT);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getEmail());
            preparedStatement.setString(3, account.getFirstName());
            preparedStatement.setString(4, account.getLastName());
            preparedStatement.setString(5, account.getAddress1());
            preparedStatement.setString(6, account.getAddress2());
            preparedStatement.setString(7, account.getState());
            preparedStatement.setString(8, account.getPhone());
            preparedStatement.setString(9, account.getZip());
            preparedStatement.setString(10, account.getCountry());
            preparedStatement.setString(11, account.getCity());
            preparedStatement.setString(12, account.getUsername());
            ResultSet resultSet = preparedStatement.executeQuery();
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void ChangeAccount(Account account){
        try {
            int bannerOpt,listOpt;
            if(account.isBannerOption()){
                bannerOpt = 1;
            }else{
                bannerOpt=0;
            }
            if(account.isListOption()){
                listOpt=1;
            }else{
                listOpt=0;
            }
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(CHANGE_ACCOUNT);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            preparedStatement.setString(3, account.getUsername());
            preparedStatement.setString(4, account.getFavouriteCategoryId());
            preparedStatement.setInt(5, bannerOpt);
            preparedStatement.setInt(6, listOpt);
            preparedStatement.setString(7, account.getUsername());
            preparedStatement.setString(8, account.getEmail());
            preparedStatement.setString(9, account.getAddress1());
            preparedStatement.setString(10, account.getAddress2());
            preparedStatement.setString(11, account.getFirstName());
            preparedStatement.setString(12, account.getLastName());
            preparedStatement.setString(13, account.getState());
            preparedStatement.setString(14, account.getCity());
            preparedStatement.setString(15, account.getCountry());
            preparedStatement.setString(16, account.getPhone());
            preparedStatement.setString(17, account.getZip());
            preparedStatement.setString(18, account.getUsername());
            preparedStatement.setString(19, account.getOldpassword());
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    private Account resultSetToAccount(ResultSet resultSet) throws Exception{
        Account account = new Account();
        account.setUsername(resultSet.getString("username"));
        account.setPassword(resultSet.getString("password"));
        account.setEmail(resultSet.getString("email"));
        account.setFirstName(resultSet.getString("firstName"));
        account.setLastName(resultSet.getString("lastName"));
        account.setStatus(resultSet.getString("status"));
        account.setAddress1(resultSet.getString("address1"));
        account.setAddress2(resultSet.getString("address2"));
        account.setCity(resultSet.getString("city"));
        account.setState(resultSet.getString("state"));
        account.setZip(resultSet.getString("zip"));
        account.setCountry(resultSet.getString("country"));
        account.setPhone(resultSet.getString("phone"));
        account.setFavouriteCategoryId(resultSet.getString("favouriteCategoryId"));
        account.setLanguagePreference(resultSet.getString("languagePreference"));
        account.setListOption(resultSet.getInt("listOption") == 1);
        account.setBannerOption(resultSet.getInt("bannerOption") == 1);
        account.setBannerName(resultSet.getString("bannerName"));
        return account;
    }
    private MyCart resultSetToMyCart(ResultSet resultSet) throws Exception{
        MyCart myCart = new MyCart();
        myCart.setQuantity(resultSet.getInt("quantity"));
        myCart.setItemId(resultSet.getString("iditem"));
        myCart.setUsername(resultSet.getString("user"));
        return myCart;
    }



    @Override
    public void updateAccount(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getEmail());
            preparedStatement.setString(3, account.getFirstName());
            preparedStatement.setString(4, account.getLastName());
            preparedStatement.setString(5, account.getStatus());
            preparedStatement.setString(6, account.getAddress1());
            preparedStatement.setString(7, account.getAddress2());
            preparedStatement.setString(8, account.getCity());
            preparedStatement.setString(9, account.getState());
            preparedStatement.setString(10, account.getZip());
            preparedStatement.setString(11, account.getCountry());
            preparedStatement.setString(12, account.getPhone());
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateProfile(Account account) {
        try {
            int mylistoption;
            int banneropt;
            Connection connection = DBUtil.getConnection();
            if(account.isListOption()){
                mylistoption =1;
            }else{
                mylistoption = 0;
            }
            if(account.isBannerOption()){
                banneropt = 1;
            }else{
                banneropt = 0;
            }
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_PROFILE);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getLanguagePreference());
            preparedStatement.setString(3, account.getFavouriteCategoryId());
            preparedStatement.setInt(4, mylistoption);
            preparedStatement.setInt(5, banneropt);
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void updateSignon(Account account) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SIGNON);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void reserveCart(Account account, Cart cart){
        try {
            Iterator<CartItem> cartItems = cart.getAllCartItems();
            while (cartItems.hasNext()) {
                Connection connection = DBUtil.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(RESERVE_CART);
                CartItem cartItem = (CartItem) cartItems.next();
                String username = account.getUsername();
                String itemId = cartItem.getItem().getItemId();//可能是寻找购物车中宠物的名字
                System.out.println(itemId);

                int quantity = cartItem.getQuantity();
                System.out.println(quantity);
                MyCart myCart = this.getMyCartByUsernameAnditemId(username,itemId);
                if(myCart != null) {
                    preparedStatement = connection.prepareStatement(UPDATE_CART);
                    preparedStatement.setInt(1, quantity);
                    preparedStatement.setString(2, account.getUsername());
                    preparedStatement.setString(3, itemId);

                }else {
                    preparedStatement.setString(1, account.getUsername());
                    preparedStatement.setString(2, itemId);
                    preparedStatement.setInt(3, quantity);
                }
                preparedStatement.execute();
                DBUtil.closePreparedStatement(preparedStatement);
                DBUtil.closeConnection(connection);
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }


    public Cart readMyCart(Account account,Cart cart){
        String username = account.getUsername();
        MyCart myCart = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(READ_MY_CART);
            preparedStatement.setString(1, username);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("11111111");
                myCart = this.resultSetToMyCart(resultSet);
                if(myCart.getUsername().equals(username) && myCart.getQuantity() != 0 ){
                    String itemId = myCart.getItemId();
                    CatalogService catalogService = new CatalogService();
                    boolean isInStock = catalogService.isItemInStock(itemId);
                    Item item = catalogService.getItem(itemId);
                    cart.addItem(item, isInStock);
                    int quantity = myCart.getQuantity();
                    cart.setQuantityByItemId(itemId,quantity);
                }
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return cart;
}






}
